One of the more elementary VBA tasks that any developer will perform is to find cells that meet some criteria. VBA and Excel support only a primitive method, the Range.Find method that requires some amount of understanding. To further compound the problem the documentation leaves a lot to be desired.
Here, we address both those issues. First, more details about the Find method, including information on how to search for cells that meet certain formatting.
Then, a “find all” capability. Microsoft enhanced the user interface to include a “find all” capability. Unfortunately, and some versions after it was introduced in the UI, it is still absent from the Excel object model. So, we look at programmatically implementing a “find all” capability at the worksheet level.
Finally, the code for Find All and several examples.
Finds specific information in a range.
expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
expression A variable that represents a Range object.
Parameters
|
Name |
Required/Optional |
Data Type |
Description |
|
What |
Required |
Variant |
The data to search for. Can be a string or any Microsoft Excel data type. When searching for all cells that match a particular format, use a zero length string (i.e., "") for this argument. |
|
After |
Optional |
Variant |
The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you do not specify this argument, the search starts after the cell in the upper-left corner of the range. |
|
LookIn |
Optional |
Variant |
The type of information. Can be xlValues, xlFormulas, or xlComments. |
|
LookAt |
Optional |
Variant |
Can be one of the following XlLookAt constants: xlWhole or xlPart. |
|
SearchOrder |
Optional |
Variant |
Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns. |
|
SearchDirection |
Optional |
The search direction. Can be xlByRows or xlByColumns. |
|
|
MatchCase |
Optional |
Variant |
True to make the search case sensitive. The default value is False. |
|
MatchByte |
Optional |
Variant |
Used only if you have selected or installed double-byte language support. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents. |
|
SearchFormat |
Optional |
Variant |
Apply search format. Can be True or False. See Remarks for more on how to use SearchFormat. |
Return Value
A Range object that represents the first cell where that information is found.
This method returns Nothing if no match is found. The Find method does not affect the selection or the active cell.
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
You can use the FindNext and FindPrevious methods to repeat the search.
Unfortunately, FindNext does not respect the SearchFormat specification. So, if the search criteria include format information, you cannot use FindNext to continue the search. See the section on using the SearchFormat argument for more on how to correctly find all cells.
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
The below example lists the addresses of all the cells in the range A1:A500 on worksheet 1 that contain the value 5.
With Worksheets(1).Range("a1:a500")
Dim C As Range
Set C = .Find(5, LookIn:=xlValues)
If Not C Is Nothing Then
Dim FirstAddress As String, Rslt As String
FirstAddress = C.Address
Do
Rslt = Rslt & C.Address & ","
Set C = .FindNext(C)
Loop While C.Address <> FirstAddress
Debug.Print Left(Rslt, Len(Rslt) - 1)
End If
End With
To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters Cour. When the code finds a match, it changes the font to Times New Roman.
For Each c In [A1:C5]
If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New
Roman"
Next c
Processing a large number of cells with a ‘For…Each’ loop may consume significant computer resources. Consequently, a consumer may perceive the behavior as an unresponsive program.
The SearchFormat argument facilitates a search for cells that match a desired format using a two-step process:
1) Specify the desired format through the CellFormat object returned by the Application.FindFormat property
2) In the Find method specify the SearchFormat argument as True
For example, to find cells that match a particular number format, use
Application.FindFormat.NumberFormat =
"General;-General;""-"""
Another example might be to display the address of the first cell that has a particular fill, say the ColorIndex 19, starting after the active cell.
With Application.FindFormat
.Clear
.Interior.ColorIndex = 19
End With
MsgBox Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Address
To specify the fill color, use ColorIndex or Color or ThemeColor. What is peculiar is that while Interior.ThemeColor works as expected, specifying a TintAndShade causes the Find method to fail. So, the below does not work.
With Range("c6").Interior
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.5
End With
With Application.FindFormat
.Clear
With .Interior
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.5
End With
End With
MsgBox Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Address
As noted above FindNext will not yield the expected result when searching for a format. So, one is forced to use the Find method inside the loop as in the example below, which lists the addresses of all the cells in the active worksheet that have a fill of red.
With Application.FindFormat
.Clear
With .Interior
.Color = RGB(255, 0, 0)
End With
End With
Dim FirstCell As Range
Set FirstCell = Cells.Find(What:="", After:=Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
If Not FirstCell Is Nothing Then
Dim CurrCell As Range, Rslt As String
Set CurrCell = FirstCell
Do
Rslt = Rslt & CurrCell.Address & ","
Set CurrCell = Cells.Find(What:="", After:=CurrCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
Loop Until CurrCell.Address = FirstCell.Address
Debug.Print Left(Rslt, Len(Rslt) - 1)
End If
Note that this approach, using the FindFormat property and the SearchFormat argument, will not find cells that display a format that is the result of conditional formatting.
For more on using the CellFormat object, see the Office 2013 documentation:
CellFormat Object (Excel) http://msdn.microsoft.com/en-us/library/office/ff839805(v=office.15).aspx
and
CellFormat Members (Excel) http://msdn.microsoft.com/en-us/library/office/ff839073.aspx
One of the things I hate about the Range collection's Find method is how cumbersome it is to set up and use. Not only is it clumsy to detect when one has processed all the cells that meet the find criteria but it is also not easy to get all the cells as a single range. The FindAll function simplifies the use of the Find method.
Function FindAll(What, Optional SearchWhat As Variant, _
Optional LookIn, _
Optional LookAt, _
Optional SearchOrder, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte, _
Optional SearchFormat) As Range
The function returns a range consisting of all the cells that match the specified criteria. Consequently, it might contain multiple areas.
FindAll supports all the same arguments that the Find method with the exception that SearchWhat replaces the After argument. SearchWhat can be
· nothing in which case the code searches the activesheet's usedrange
· a specific worksheet or a single cell in which case the code searches that worksheet's usedrange, or
· a specific range in which case the code searches just that range
To search for cells that meet a format criteria, use the same approach as in the section discussing the SearchFormat argument. Specifically, use the Application.FindFormat property to specify the desired format. Then, use the FindAll function with the SearchFormat argument set to True.
In the example below, the code searches column C of the used range for all cells that match the specified number format.
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General;-General;""-"""
MsgBox FindAll("", Application.Intersect( _
ActiveSheet.UsedRange, Range("c:c")), _
xlFormulas, xlPart, SearchFormat:=True).Address
Below is the code for the FindAll function. There should be no need to modify it for any custom criteria. It is general enough that it can be used as a “drag and drop” solution.
Option Explicit
Function FindAll(What, Optional SearchWhat As Variant, _
Optional LookIn, _
Optional LookAt, _
Optional SearchOrder, _
Optional SearchDirection As XlSearchDirection = xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte, _
Optional SearchFormat) As Range
'LookIn can be xlValues or xlFormulas, _
LookAt can be xlWhole or xlPart, _
SearchOrder can be xlByRows or xlByColumns, _
SearchDirection can be xlNext, xlPrevious, _
MatchCase, MatchByte, and SearchFormat can be True or False. _
Before using SearchFormat = True, specify the appropriate settings _
for the Application.FindFormat object, e.g., _
Application.FindFormat.NumberFormat = "General;-General;""-"""
Dim aRng As Range
If IsMissing(SearchWhat) Then
On Error Resume Next
Set aRng = ActiveSheet.UsedRange
On Error GoTo 0
ElseIf TypeOf SearchWhat Is Range Then
If SearchWhat.Cells.Count = 1 Then
Set aRng = SearchWhat.Parent.UsedRange
Else
Set aRng = SearchWhat
End If
ElseIf TypeOf SearchWhat Is Worksheet Then
Set aRng = SearchWhat.UsedRange
Else
Exit Function '*****
End If
If aRng Is Nothing Then Exit Function '*****
Dim FirstCell As Range, CurrCell As Range
With aRng.Areas(aRng.Areas.Count)
Set FirstCell = .Cells(.Cells.Count)
'This little 'dance' ensures we get the first matching _
cell in the range first
End With
Set FirstCell = aRng.Find(What:=What, After:=FirstCell, _
LookIn:=LookIn, LookAt:=LookAt, _
SearchDirection:=SearchDirection, MatchCase:=MatchCase, _
MatchByte:=MatchByte, SearchFormat:=SearchFormat)
If FirstCell Is Nothing Then Exit Function '*****
Set CurrCell = FirstCell
Set FindAll = CurrCell
Do
Set FindAll = Application.Union(FindAll, CurrCell)
'Setting FindAll at the top of the loop ensures _
the result is arranged in the same sequence as _
the matching cells; the duplicate assignment of _
the first matching cell to FindAll being a small _
price to pay for the ordered result
Set CurrCell = aRng.Find(What:=What, After:=CurrCell, _
LookIn:=LookIn, LookAt:=LookAt, _
SearchDirection:=SearchDirection, MatchCase:=MatchCase, _
MatchByte:=MatchByte, SearchFormat:=SearchFormat)
'FindNext is not reliable because it ignores the FindFormat settings
Loop Until CurrCell.Address = FirstCell.Address
End Function
Below is a subroutine with several examples of how to use FindAll.
Sub ExamplesOfFindAll()
'reset any prior find format condition
Application.FindFormat.Clear
'show the address of the range in the activesheet _
that contains a value of 1
MsgBox FindAll(1, , xlValues, xlWhole).Address
'show the address of the range in the activesheet _
that contains 1 as any part of the value
MsgBox FindAll(1, , xlValues, xlPart).Address
'show the address of the range in the activesheet _
where the formula contains a open paren
MsgBox FindAll("(", , xlFormulas, xlPart).Address
'show the address of the cells in column C of the activesheet _
that contain a zero
Application.FindFormat.Clear
Dim Rslt As Range
MsgBox FindAll(0, Range("c:c"), xlFormulas, xlWhole).Address
'if a custom number format applies to the entire column C, the below _
will cause a major performance headache because the find will step _
through every cell in column C!
'MsgBox FindAll("", Range("c:c"), _
xlFormulas, xlPart, SearchFormat:=True).Address
'An alternative to the above is to limit the search to the usedrange.
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General;-General;""-"""
MsgBox FindAll("", Application.Intersect( _
ActiveSheet.UsedRange, Range("c:c")), _
xlFormulas, xlPart, SearchFormat:=True).Address
'show the address of the range in column C that contains a zero and _
the specified custom number format
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General;-General;""-"""
MsgBox FindAll(0, Range("c:c"), _
xlFormulas, xlWhole, SearchFormat:=True).Address
'show the address of the range of cells in column C within the _
activesheet's usedrange that have a fill color of xlThemeColorAccent2
Application.FindFormat.Clear
Application.FindFormat.Interior.ThemeColor = xlThemeColorAccent2
MsgBox FindAll("", Application.Intersect( _
ActiveSheet.UsedRange, Range("c:c")), _
xlFormulas, xlPart, SearchFormat:=True).Address
End Sub
[1] Most of the information for the Range.Find method is from the Microsoft online documentation. I have added missing content, clarified other content, made corrections, and provided additional information about the SearchFormat parameter.